
USE [GrewalCRM_DB]
GO

/****** Object:  StoredProcedure [usp_GetCallsByID]    Script Date: 3/17/2013 8:51:00 PM ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[usp_GetCallsByID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [usp_GetCallsByID];
GO

CREATE PROCEDURE [usp_GetCallsByID]
(
	@CallID int
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[CallID],
		[CallNo],
		[CallReceiveDate],
		[CallTypeID],
		[DealerID],
		[CustomerID],
		[CallBookedDate],
		[InstallerID],
		[CallAssignedDate],
		[CallCompletedDate],
		[Remarks],
		[CallStatusID],
		[ACBrand],
		[ACParchasedDate],
		[ACModel],
		[ACSerialNo],
		[LastModifiedUser],
		[LastModifiedDateTime]
	FROM [Calls]
	WHERE
		([CallID] = @CallID)

	SET @Err = @@Error

	RETURN @Err
END
GO


/****** Object:  StoredProcedure [usp_GetAllCalls]    Script Date: 3/17/2013 8:51:00 PM ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[usp_GetAllCalls]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [usp_GetAllCalls];
GO

CREATE PROCEDURE [usp_GetAllCalls]
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[CallID],
		[CallNo],
		[CallReceiveDate],
		[CallTypeID],
		[DealerID],
		[CustomerID],
		[CallBookedDate],
		[InstallerID],
		[CallAssignedDate],
		[CallCompletedDate],
		[Remarks],
		[CallStatusID],
		[ACBrand],
		[ACParchasedDate],
		[ACModel],
		[ACSerialNo],
		[LastModifiedUser],
		[LastModifiedDateTime]
	FROM [Calls]

	SET @Err = @@Error

	RETURN @Err
END
GO


/****** Object:  StoredProcedure [usp_UpdateCalls]    Script Date: 3/17/2013 8:51:00 PM ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[usp_UpdateCalls]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [usp_UpdateCalls];
GO

CREATE PROCEDURE [usp_UpdateCalls]
(
	@CallID int,
	@CallNo varchar(50),
	@CallReceiveDate datetime,
	@CallTypeID int,
	@DealerID int,
	@CustomerID int,
	@CallBookedDate datetime = NULL,
	@InstallerID int = NULL,
	@CallAssignedDate datetime = NULL,
	@CallCompletedDate datetime = NULL,
	@Remarks varchar(MAX) = NULL,
	@CallStatusID int = NULL,
	@ACBrand varchar(50),
	@ACParchasedDate datetime,
	@ACModel varchar(50),
	@ACSerialNo varchar(50),
	@LastModifiedUser int,
	@LastModifiedDateTime datetime
)
AS
BEGIN

	SET NOCOUNT OFF
	DECLARE @Err int

	UPDATE [Calls]
	SET
		[CallNo] = @CallNo,
		[CallReceiveDate] = @CallReceiveDate,
		[CallTypeID] = @CallTypeID,
		[DealerID] = @DealerID,
		[CustomerID] = @CustomerID,
		[CallBookedDate] = @CallBookedDate,
		[InstallerID] = @InstallerID,
		[CallAssignedDate] = @CallAssignedDate,
		[CallCompletedDate] = @CallCompletedDate,
		[Remarks] = @Remarks,
		[CallStatusID] = @CallStatusID,
		[ACBrand] = @ACBrand,
		[ACParchasedDate] = @ACParchasedDate,
		[ACModel] = @ACModel,
		[ACSerialNo] = @ACSerialNo,
		[LastModifiedUser] = @LastModifiedUser,
		[LastModifiedDateTime] = @LastModifiedDateTime
	WHERE
		[CallID] = @CallID


	SET @Err = @@Error


	RETURN @Err
END
GO




/****** Object:  StoredProcedure [usp_InsertCalls]    Script Date: 3/17/2013 8:51:00 PM ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[usp_InsertCalls]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [usp_InsertCalls];
GO

CREATE PROCEDURE [usp_InsertCalls]
(
	@CallID int = NULL output,
	@CallNo varchar(50),
	@CallReceiveDate datetime,
	@CallTypeID int,
	@DealerID int,
	@CustomerID int,
	@CallBookedDate datetime = NULL,
	@InstallerID int = NULL,
	@CallAssignedDate datetime = NULL,
	@CallCompletedDate datetime = NULL,
	@Remarks varchar(MAX) = NULL,
	@CallStatusID int = NULL,
	@ACBrand varchar(50),
	@ACParchasedDate datetime,
	@ACModel varchar(50),
	@ACSerialNo varchar(50),
	@LastModifiedUser int,
	@LastModifiedDateTime datetime
)
AS
BEGIN

	SET NOCOUNT OFF
	DECLARE @Err int

	INSERT
	INTO [Calls]
	(
		[CallNo],
		[CallReceiveDate],
		[CallTypeID],
		[DealerID],
		[CustomerID],
		[CallBookedDate],
		[InstallerID],
		[CallAssignedDate],
		[CallCompletedDate],
		[Remarks],
		[CallStatusID],
		[ACBrand],
		[ACParchasedDate],
		[ACModel],
		[ACSerialNo],
		[LastModifiedUser],
		[LastModifiedDateTime]
	)
	VALUES
	(
		@CallNo,
		@CallReceiveDate,
		@CallTypeID,
		@DealerID,
		@CustomerID,
		@CallBookedDate,
		@InstallerID,
		@CallAssignedDate,
		@CallCompletedDate,
		@Remarks,
		@CallStatusID,
		@ACBrand,
		@ACParchasedDate,
		@ACModel,
		@ACSerialNo,
		@LastModifiedUser,
		@LastModifiedDateTime
	)

	SET @Err = @@Error

	SELECT @CallID = SCOPE_IDENTITY()

	RETURN @Err
END
GO


/****** Object:  StoredProcedure [usp_DeleteCalls]    Script Date: 3/17/2013 8:51:00 PM ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[usp_DeleteCalls]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [usp_DeleteCalls];
GO

CREATE PROCEDURE [usp_DeleteCalls]
(
	@CallID int
)
AS
BEGIN

	SET NOCOUNT OFF
	DECLARE @Err int

	DELETE
	FROM [Calls]
	WHERE
		[CallID] = @CallID
	SET @Err = @@Error

	RETURN @Err
END
GO

